import pandas as pd
import json

# 读取Excel文件
df = pd.read_excel('gf.xlsx')

# 创建一个字典来存储最终的JSON数据
result = {}

# 处理每一行数据
for index, row in df.iterrows():
    province_code = row['province_geocode']
    area_code = row['areacode']
    city_code = row['city_geocode']
    # district_code = row['district_geocode']
    province_name = row['province']
    city_name = "市辖区" if row['province'] == row['city'] else row['city']
    district_name = row['district']

    # 如果省份不存在，创建省份字典
    if province_code not in result:
        result[province_code] = {
            "code": province_code,
            "name": province_name,
            "children": []
        }

    # 找到省份的城市列表
    cities = result[province_code]["children"]

    # 查找或创建城市字典
    city = next((item for item in cities if item["code"] == city_code), None)
    if city is None:
        city = {
            "code": city_code,
            "name": city_name,
            "children": []
        }
        cities.append(city)

    # 添加区县
    city["children"].append({
        "code": area_code,
        "name": district_name
    })

# 将结果字典转换为列表
result_list = list(result.values())

# 转换为JSON格式并输出
json_result = json.dumps(result_list, ensure_ascii=False, indent=2)
print(json_result)

# 将JSON数据写入文件
with open('result.json', 'w', encoding='utf-8') as f:
    f.write(json_result)
